import pandas as pd
import pymysql

# 读取数据
df = pd.read_csv('dalian_weather_2024.csv', encoding='utf-8')
df1 = pd.read_csv('future_weather_predictions2023-2024.csv', encoding='utf-8')
# print(df)
list = []
for i in range(len(df)):
    list.append((df['日期'][i], df['最高气温'][i], df['最低气温'][i], df['天气'][i], df['风级'][i]))
# print(list)
list2 = []
for i in range(len(df1)):
    list2.append((df1['日期'][i], df1['预测最高气温'][i], df1['预测最低气温'][i]))
#数据库连接
con = pymysql.connect(user='root', password='123456', host='localhost', database='demo', port=3306)
cur = con.cursor()
#表格创建
sql = '''
create table if not exists weather (weather_data varchar(255),maxTem varchar(255),minTem varchar(255),weatherNow varchar(255),wind varchar(255))
'''
sql2 = '''
create table if not exists future_weather (weather_data varchar(255),maxTem varchar(255),minTem varchar(255))
'''
try:
    cur.execute(sql)
    cur.execute(sql2)
except Exception as e:
    print(e)
    print(" ")
else:
    print("创建成功")

# 数据插入
sql='''
    insert into weather(weather_data,maxTem,minTem,weatherNow,wind)
    values (%s,%s,%s,%s,%s)
'''
sql2='''
    insert into future_weather(weather_data,maxTem,minTem)
    values (%s,%s,%s)
'''
try:

    cur.executemany(sql,list)
    cur.executemany(sql2,list2)
    con.commit()
except Exception as e:
    print(e)
    print("lose")
    con.rollback()
else:
    print('插入成功')

# 查询
sql ='''
    select * from weather
'''
try:
    cur.execute(sql)
    weather =cur.fetchall()
    print(weather)
except Exception as e:
    print(e)
    print('lose')
else:
    print('查询成功')
finally:
    con.close()




